Online Transaction Processing Research Articles

Read-safe snapshots: An abort/wait-free serializable read method for read-only transactions on mixed OLTP/OLAP workloads

This paper proposes Read-Safe Snapshots (RSS), a concurrency control method that ensures reading the latest serializable version on multiversion concurrency control (MVCC) for read-only transactions without creating any serializability anomaly, thereby enhancing the transaction processing throughput under mixed workloads of online transactional processing (OLTP) and online analytical processing (OLAP). Ensuring serializability for data consistency between OLTP and OLAP is vital to prevent OLAP from obtaining nonserializable results. Existing serializability methods achieve this consistency by making OLTP or OLAP transactions aborts or waits, but these can lead to throughput degradation when implemented for large read sets in read-only OLAP transactions under mixed workloads of the recent real-time analysis applications. To deal with this problem, we present an RSS construction algorithm that does not affect the conventional OLTP performance and simultaneously avoids producing additional aborts and waits. Moreover, the RSS construction method can be easily applied to the read-only replica of a multinode system as well as a single-node system because no validation for serializability is required. Our experimental findings showed that RSS could prevent read-only OLAP transactions from creating anomaly cycles under a multinode environment of master-copy replication, which led to the achievement of serializability with the low overhead of about 15% compared to baseline OLTP/OLAP throughputs under snapshot isolation (SI). The OLTP throughput under our proposed method in a mixed OLTP/OLAP workload was about 45% better than SafeSnapshots, a serializable snapshot isolation (SSI) equipped with a read-only optimization method, and did not degrade the OLAP throughput.

Implementation and evaluation of a decentralized medical data exchange system based on the IOTA Tangle and approximate algorithms for optimal data placement


 
 
 
 
 
 
 
 ©   Voronin V., Rybalchenko A., Shmatko O., Kolomiitsev O., Tretiak V., Kliuchka Ya., 2023
 
 
 
 
  
 
 
 
 Facilitating the transfer of patient medical information across healthcare providers is crucial for ensuring high-quality care. However, this process encounters obstacles pertaining to privacy, security, and centralised control. This article outlines the creation of a decentralised system for exchanging medical data. The system utilises Distributed Ledger Technology, specifically the IOTA Tangle (open protocol (and network) for data and value transfer). This document outlines the structure and fundamental elements of a system designed for the secure and unalterable storage and transfer of medical records. The system employs masking and encryption methodologies to protect patient confidentiality while enabling healthcare practitioners to access complete information with patient authorization. The IOTA Tangle facilitates transactions without any fees and ensures the verification of data integrity through its Directed Acyclic Graph (DAG) topology. Simulation trials validate the system’s capability to securely communicate medical data on a large scale, while incurring lower resource costs than typical blockchain systems. The system demonstrates the feasibility of a decentralised and self-governing method for efficient and confidential sharing of medical data, utilising Distributed Ledger Technology (DLT). The secure data sharing platform facilitates the implementation of novel care and research frameworks, all the while upholding patient confidentiality and adhering to healthcare ethical standards. Also, in the development of billing Online Transaction Processing (OLTP) systems, which are designed for input, structured storage and processing of information in real time, the use of cloud technology is proposed. The problems that arise and their relevance to the solution of an integer linear programming problem with Boolean variables are shown. Approximate algorithms for optimal data placement and mathematical models for optimizing the structure of a distributed database of a cloud system are proposed, taking into account the limitations on the amount of node memory, the available costs of renting cloud resources, and the number of replicas of fragments of the distributed database.

Impact of business intelligence on incident management in the control center of a security company

The main objective of this article is to implement business intelligence (BI) technology to improve incident management in the control center of a security company; making use of the Ralph Kimball method for the development of the data mart, contemplating the phases: planning of projects, definition of requirements, design of technological architecture, performing a dimensional modeling a star with 9 dimensions and 1 table made, also used the structured query language (MySQL) database, extract, transform, and load (ETL). Development the pentaho data integration, which allowed the migration, transformation and cleaning of the data from the online transaction processing (OLTP) and online analytical processing (OLAP) database, obtaining as a result that the BI managed to increase the level of the service by 28.42% and reducing the frequency index by 25%.

Proposed framework for enhancing integrity technique using distributed query operation

Database growth and Storage problems are main concern for large and small enterprises nowadays, which significantly influences the efficiency of database applications. Database archiving is one of the solutions available for management. Many issues have been identified as a result of the use of archive databases, including the elimination of inactive data from online transaction processing systems (OLTP), performance and integrity management. The aim of this paper is to propose a framework for storing OLTP and archiving data in a distributed database environment as part of an integrated system. Maintaining the integrity between an OLTP database and an archiving database is crucial, but even more critical is the performance of the required query. The main aspect of the proposed framework is that it will not only ensure data integrity for primary and unique keys between OLTP and archive database, but it will also improve query performance by introducing parallel processing and query execution plans to maintain that integrity.

H[formula omitted]-RAID: Improving the reliability of SSD RAID with unified SSD and HDD hybrid architecture

With the increasing development of SSD (Solid-State Drives) technology, SSD RAID (Redundant Arrays of Independent Disks) has been widely deployed in enterprise data centers. However, the inherent write endurance issue of SSD seriously affects the reliability of the array. Meanwhile, compared with conventional HDD-based RAID, SSD RAID exhibits very different failure characteristics, such as correlated failure (Balakrishnan et al., 2010) under RAID-5. In this paper, we present a Hybrid High reliability RAID architecture, named H2-RAID, by equipping each SSD with an extra HDD as the backup to improve the reliability of SSD RAID. Considering the relatively longer write latency of HDD, in H2-RAID, we first propose an HDD-aware backup mechanism to smartly aggregate random writes into sequential writes to decrease performance degradation. In addition, to cope with the scenarios of SSD failure, an HDD-aware reconstruction method is further proposed to guarantee the reliability and the online transaction processing performance. We build a novel Markov process-based mathematical model to analyze the reliability of different architectures, and the theoretical results prove the reliability of H2-RAID is much higher than that of traditional SSD RAID. To more accurately evaluate the performance influence of HDD on H2-RAID, we develop a simulator based on Disksim and the experimental results show H2-RAID significantly increases the reliability compared with SSD array (under RAID-5) while with little performance loss on average.

Learning Graph Databases: Neo4j an overview

As the internet is growing day by day, the amount of data being generated is huge. This data includes structured data and unstructured data. The data along with its relationship with other data makes the most powerful and meaningful information. Maximum data exists in the form of the relationship between different or same objects and the noticeable thing is the relationship between the data is more important than the data itself. These relationships are handled efficiently by Relational databases that store data having structures and which have several records. The important point to be noted here is that these Relational database management Systems use tables with normalization concept. If the amount of data in such tables is huge, then handling such a large amount of data with its relationships is a tedious task. Here, Graph Databases come into picture. Entities and their relationships in relational databases will be reflected with nodes and relationships in graph databases. Graph databases provide very simple data model than databases with Online Transaction Processing systems. Graph databases provide features such as transactional integrity and operational availability. This paper introduces the idea of graph database systems in conjunction with Neo4j encompassing its query features, consistency, transactions, availability, and scaling.

DBPA: A Benchmark for Transactional Database Performance Anomalies

Anomaly diagnosis is vital to the performance of online transaction processing (OLTP) systems. In the meanwhile, machine learning techniques can reason complex relationships beyond human abilities and perform well on such problems. However, they rely on a large number of training samples for anomalies, which are in serious shortage in both industry and academia due to the difficulty of collection. The problem raises the demand of a benchmark for anomaly reproduction and data collection. In this paper, we propose DBPA, a benchmark for transactional database performance anomalies. Specifically, we identify nine common anomalies rooted in the diverse influence factors. For each anomaly, we carefully design a reproduction procedure, which consists with its root cause in real-world databases. With the reproduction procedures, users can easily generate a dataset in a new environment and extend new anomaly types. For compound anomalies, we provide a generation algorithm that allows users to generate compound anomalies data of any possible combinations with existing collected data. We also provide a large dataset of both normal and anomalous monitoring data collected from various environments, facilitating the training of machine learning models and the evaluation of new algorithms for anomaly diagnosis.

One-shot Garbage Collection for In-memory OLTP through Temporality-aware Version Storage

Most modern in-memory online transaction processing (OLTP) engines rely on multi-version concurrency control (MVCC) to provide data consistency guarantees in the presence of conflicting data accesses. MVCC improves concurrency by generating a new version of a record on every write, thus increasing the storage requirements. Existing approaches rely on garbage collection and chain consolidation to reduce the length of version chains and reclaim space by freeing unreachable versions. However, finding unreachable versions requires the traversal of long version chains, which incurs random accesses right into the critical path of transaction execution, hence limiting scalability. This paper introduces OneShotGC, a new multi-version storage design that eliminates version traversal during garbage collection, with minimal discovery and memory management overheads. OneShotGC leverages the temporal correlations across versions to opportunistically cluster them into contiguous memory blocks that can be released in one shot. We implement OneShotGC in Proteus and use YCSB and TPC-C to experimentally evaluate its performance with respect to the state-of-the-art, where we observe an improvement of up to 2x in transactional throughput.

SQL and NoSQL Database Software Architecture Performance Analysis and Assessments—A Systematic Literature Review

The competent software architecture plays a crucial role in the difficult task of big data processing for SQL and NoSQL databases. SQL databases were created to organize data and allow for horizontal expansion. NoSQL databases, on the other hand, support horizontal scalability and can efficiently process large amounts of unstructured data. Organizational needs determine which paradigm is appropriate, yet selecting the best option is not always easy. Differences in database design are what set SQL and NoSQL databases apart. Each NoSQL database type also consistently employs a mixed-model approach. Therefore, it is challenging for cloud users to transfer their data among different cloud storage services (CSPs). There are several different paradigms being monitored by the various cloud platforms (IaaS, PaaS, SaaS, and DBaaS). The purpose of this SLR is to examine the articles that address cloud data portability and interoperability, as well as the software architectures of SQL and NoSQL databases. Numerous studies comparing the capabilities of SQL and NoSQL of databases, particularly Oracle RDBMS and NoSQL Document Database (MongoDB), in terms of scale, performance, availability, consistency, and sharding, were presented as part of the state of the art. Research indicates that NoSQL databases, with their specifically tailored structures, may be the best option for big data analytics, while SQL databases are best suited for online transaction processing (OLTP) purposes.

Arriving at the Results by Comparing with Traditional Approach to My Approach in Deriving Function Points for ETL Operations

It can be hard to guess how much data will need to be put into the data warehouse when the whole history of the transaction system is moved there. This is especially true when the transfer process could take weeks or even months. The ETL system's parts must be broken down into its three independent stages, nevertheless, when estimating a big starting load. Data extraction from source systems, Creating the dimensional model from data, Loading the data warehouse and timing estimates for the extraction process. Surprisingly, data extraction from the source system may take up the majority of the ETL procedure. Online transaction processing (OLTP) systems are simply not built to return those massive data sets from the data warehouse's historic load, which extracts a tremendous quantity of data in a single query. However, the daily incremental loads and the breath-of-life historic database loads are very different. In any case, fact-table filling requires data to be pulled in a different way than what transaction systems are able to do. ETL extraction procedures frequently call for time-consuming techniques like views, cursors, stored procedures, and correlated subqueries. It is essential to anticipate how long an extract will take to begin before it does. Calculating the extract time estimate is challenging. Due to the hardware mismatch between the test and production servers, estimates based on the execution of the ETL operations in the test environment may be greatly distorted. Sometimes working on certain projects where an extract task would run continuously and until it eventually failed, at which point it would be restarted and run once more until it failed. Without producing anything, days or even weeks passed. One must divide the extract process into two simpler steps in order to overcome the challenges of working with large amounts of data. Response time for queries. the interval between when the query is conducted and when the data starts to be returned. It is pertinent that effort arrival for ETL Operations for Data Marts and DWH projects in terms of Function Points which is a scientific way is essential. In the last paper, I have talked about general System Characteristics to arrive at Value Adjustment Factor. In this paper, I came up with results. I compared my findings with the conventional FPA on industrial projects in order to evaluate the Function Point Analysis’s suitability for Data Mart projects. I outline the strategy, implementation, and outcomes analysis of this validation in this section.

RDMA-Enabled Concurrency Control Protocols for Transactions in the Cloud Era

Online transaction processing (OLTP) is widely used on modern cloud infrastructures to complete important businesses such as payments and stock exchanges. Remote Direct Memory Access (RDMA) is a technology that enables ultra-low inter-server memory access latency - which is critical for implementing high-performance concurrency control protocols in distributed OLTP. In this article, we develop RCC, the first unified and comprehensive RDMA-enabled distributed transaction processing framework containing six serializable concurrency control protocols—not only the classical protocols <inline-formula><tex-math notation="LaTeX">${{\sf NOWAIT}}$</tex-math></inline-formula> , <inline-formula><tex-math notation="LaTeX">${{\sf WAITDIE}}$</tex-math></inline-formula> , <inline-formula><tex-math notation="LaTeX">${{\sf OCC}}$</tex-math></inline-formula> , but also more advanced <inline-formula><tex-math notation="LaTeX">${{\sf MVCC}}$</tex-math></inline-formula> , <inline-formula><tex-math notation="LaTeX">${{\sf SUNDIAL}}$</tex-math></inline-formula> , and <inline-formula><tex-math notation="LaTeX">${{\sf CALVIN}}$</tex-math></inline-formula> — the deterministic protocol. Our goal is to unbiasedly compare protocols on OLTP workloads in a common execution environment with the concurrency control protocol being the only changeable component. From RCC, we obtained new insights on building RDMA-based protocols. We analyzed stage-wise latency breakdown to develop more efficient hybrid implementations. Moreover, RCC can enumerate all stage-wise hybrid designs under a given workload characteristic. Our results show that throughput-wise hybrid designs are better than RPC or one-sided counterparts by 32.2% and up to 67%; three hybrid designs are better than their pure counterparts by up to 17.8%. RCC can provide performance insights and be used as the common open-sourced infrastructure for fast prototyping new implementations.

Hybrid Graph Partitioning with OLB Approach in Distributed Transactions

Online Transaction Processing (OLTP) gets support from data partitioning to achieve better performance and scalability. The primary objective of database and application developers is to provide scalable and reliable database systems. This research presents a novel method for data partitioning and load balancing for scalable transactions. Data is efficiently partitioned using the hybrid graph partitioning method. Optimized load balancing (OLB) approach is applied to calculate the weight factor, average workload, and partition efficiency. The presented approach is appropriate for various online data transaction applications. The quality of the proposed approach is examined using OLTP database benchmark. The performance of the proposed methodology significantly outperformed with respect to metrics like throughput, response time, and CPU utilization.

Digital Islamic Business Ethics: Video Unboxing as a Khiyar mechanism in Online Buying and Selling Transactions

This article describes unboxing video content as a khiyar mechanism in online transactions. The sale and purchase transaction is declared invalid if one of the parties feels compelled and disadvantaged due to product defects. The method used in this article is a field research study with a phenomemological approach. The findings of this study are that video unboxing becomes the mechanism of choice for buyers to Khiyar or the choice to continue or cancel the transaction made. Buyers who upload unboxing videos showing product defects are given the opportunity to cancel the transaction or exchange it for a new product. This mechanism arises because sellers and buyers do not meet directly in the online transaction process. Video unboxing is done as a form of awareness and responsibility (responsibility) in muamalah as a form of ethics in business. The unboxing video will ultimately give pleasure to both the seller and the buyer as a condition for the fulfillment of blessings in the transaction

Development of Business Intelligence For Integration of Financial Reporting System In PT XYZ

The research aims to determine the relationship between two or more variables. Data collection with Literature and field research with questionnaires. The result of the research that is more influential in BAPPEDA Pemprov DKI Jakarta is the Business Intelligence variable, so it needs to be prioritized and given special attention to its implementation in the field. The research method used is associative research, designing Data Warehouse architecture, planning Data Warehouse sources, dimensional data modeling, filling in Data Warehouse. And the results of this study can be used as a reference for further follow-up with the DKI Jakarta Provincial Government BAPPEDA to be prioritized in the near future. The conclusion from the results of this study is that a transaction data warehouse designed using PDI (Pentaho Data Integration), is very helpful in collecting transaction data from original transaction data that already has a database and only as OLTP (Online Transactional Processing) data so that can be used as data OLAP. (Online Analysis Processing) so that it is analyzed using OLAP.

SQLite

In the two decades following its initial release, SQLite has become the most widely deployed database engine in existence. Today, SQLite is found in nearly every smartphone, computer, web browser, television, and automobile. Several factors are likely responsible for its ubiquity, including its in-process design, standalone codebase, extensive test suite, and cross-platform file format. While it supports complex analytical queries, SQLite is primarily designed for fast online transaction processing (OLTP), employing row-oriented execution and a B-tree storage format. However, fueled by the rise of edge computing and data science, there is a growing need for efficient in-process online analytical processing (OLAP). DuckDB, a database engine nicknamed "the SQLite for analytics", has recently emerged to meet this demand. While DuckDB has shown strong performance on OLAP benchmarks, it is unclear how SQLite compares. Furthermore, we are aware of no work that attempts to identify root causes for SQLite's performance behavior on OLAP workloads. In this paper, we discuss SQLite in the context of this changing workload landscape. We describe how SQLite evolved from its humble beginnings to the full-featured database engine it is today. We evaluate the performance of modern SQLite on three benchmarks, each representing a different flavor of in-process data management, including transactional, analytical, and blob processing. We delve into analytical data processing on SQLite, identifying key bottlenecks and weighing potential solutions. As a result of our optimizations, SQLite is now up to 4.2X faster on SSB. Finally, we discuss the future of SQLite, envisioning how it will evolve to meet new demands and challenges.

SwitchTx

Online-transaction-processing (OLTP) applications require the underlying storage system to guarantee consistency and serializability for distributed transactions involving large numbers of servers, which tends to introduce high coordination cost and cause low system performance. In-network coordination is a promising approach to alleviate this problem, which leverages programmable switches to move a piece of coordination functionality into the network. This paper presents a fast and scalable transaction processing system called SwitchTx. At the core of SwitchTx is a decentralized multi-switch in-network coordination mechanism, which leverages modern switches' programmability to reduce coordination cost while avoiding the central-switch-caused problems in the state-of-the-art Eris transaction processing system. SwitchTx abstracts various coordination tasks (e.g., locking, validating, and replicating) as in-switch gather-and-scatter (GaS) operations, and offloads coordination to a tree of switches for each transaction (instead of to a central switch for all transactions) where the client and the participants connect to the leaves. Moreover, to control the transaction traffic intelligently, SwitchTx reorders the coordination messages according to their semantics and redesigns the congestion control combined with admission control. Evaluation shows that SwitchTx outperforms current transaction processing systems in various workloads by up to 2.16X in throughput, 40.4% in latency, and 41.5% in lock time.

Web Portal for Online Agri-Auction

Abstract: In this project, an online auction system is presented. It has a very large potential market of sellers and buyers. An On Line Transaction Processing (OLTP) database model structure is, therefore, desirable. The project involves the design and implementation of an online auction system. The analysis stage is performed first for the case study.Project begins by analyzing and presenting the OLTP database model for the online auction house. Besides, the intention to establish what goes on operationally within the online auction house. It also defines the layout in android application. In which many number of bidders bids the auction and final the auction won will store the result of the winning bidder details. And finally the shipment process will carried out

Data Mining Technologies in Solving Economic Problems

Improving the efficiency of business process management is a complex and extremely important task, the solution of which is unthinkable without the use of advanced information systems and management decision technologies. The paper presents the results of research on data mining technologies using On-Line Transaction Processing and On-Line Analytical Processing methods. The study proposed a generalized hierarchical representation of data processing methodologies with integration of heterogeneous sources to management decision making. Data mining base methods are systematized: classification, regression, prediction, clustering, interdependence, visualization, deviation (anomaly) detection, estimation, and feature selection (engineering). We described the possibilities of using data mining in the field of information technology, marketing, trade, financial and insurance activities. The study examined data mining technologies in sport products business using the Microsoft® Excel® application and a special add-in of the Microsoft® SQL Server® relational database management system, capable of identifying implicit (hidden) factors that affect or, equally important, do not affect sales volumes.

OLAP analysis of user energy consumption based on multitemporal distribution characteristics

With the development of databases, online transaction processing (OLTP) can no longer meet the needs of end users for database query and analysis, and the simple query of large databases by SQL can not meet the requirements of end user analysis. Therefore, online analytical processing (OLAP) is proposed. concept. On the one hand, we explained the basic knowledge of OLAP, including OLAP multidimensional data concept, multidimensional data structure, multidimensional data analysis, characteristics, etc. On the other hand, we established an OLAP analysis model of the multi-temporal and spatial distribution characteristics of user energy consumption, which is refined provide support for the analysis of user energy consumption behavior.

Analyses the Performance of Data Warehouse Architecture Types

The concept of storing historical data for retrieving them when needed has been conceived, and the idea was primitive to build repositories for historical data to store these data, despite the use of a specific technique for recovering these data from various storage modes. The data warehouse is the most reliable and widely used technology for scheduling, forecasting, and managing corporations. Also, it is concerned with the data storage facility that extensive collection of data. Data warehouses are called ancient modern techniques; since the early days of relational databases, the critical component of decision support, increasing focus for the database industry. Many commercial products and services are now available, and almost all of the primary database management system vendors provide them. When opposed to conventional online transaction processing applications, decision support puts slightly different demands on database technology. This paper analyzes the performance of the data warehouse architectures by studying and comparing many research works in this field. The study involves extracting, transforming, and loading the data from different recourses and the important characteristic of the architecture types. Furthermore, the tools and application service techniques used to build data warehouse architecture.